BigQueyの列レベル制御でさまざまな操作をしてみる
2024.09.05はじめに
データアナリティクス事業本部のkobayashiです。
BigQueryのアクセス制限にデータのポリシータグを使った列レベルの制御があります。これはテーブルの特定の列に対するアクセス制御を行うことが可能な機能できめ細かいアクセス制御を行うことができます。今回はこの列レベルのアクセス制御を行ったテーブルに対してどのような様々な操作を行って列レベルの制御がどう行われるかを確かめてみたいと思います。
BigQueryの列レベル制御とは
BigQueryの列レベル制御は、BigQueryのセキュリティを強化するための重要な機能です。この機能を使うと、特定のユーザーやグループに対して、テーブル内の特定の列へのアクセスを制限することができます。
- 細かいアクセス制御： テーブル全体ではなく、個別の列に対してアクセス権限を設定できます。
- データ保護： 機密情報や個人情報を含む列へのアクセスを制限し、データのセキュリティを向上させます。
- コンプライアンス対応： データ保護規制（GDPRなど）への準拠を支援します。
- 柔軟な権限設定： ユーザーやグループごとに異なるアクセス権限を設定できます。
列レベル制御を効果的に使用することで、BigQueryでのデータ管理がより安全かつ柔軟になります。
列レベルのアクセス制御の設定方法は下記エントリで詳しく説明されているのでご一読ください。
BigQueryの列レベル制御を試してみる
対象となるテーブル情報
それではポリシータグを設定し保護されている列に対して様々な操作をして見たいと思います。使うテーブル、ポリシータグ、操作はBigQueyの下記ドキュメントに従って試してみます。
対象となるテーブルにはuser_id,credit_score,ssnの3つの列がありpolicy-tag-1,policy-tag-2,policy-tag-3のポリシータグが設定されている状態です。
$ bq show --schema --format=prettyjson access_control.customers
[
{
"mode": "REQUIRED",
"name": "user_id",
"policyTags": {
"names": [
"projects/{Project ID}/locations/us/taxonomies/1234567890/policyTags/111111111111"
]
},
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "credit_score",
"policyTags": {
"names": [
"projects/{Project ID}/locations/us/taxonomies/1234567890/policyTags/2222222222"
]
},
"type": "INTEGER"
},
{
"mode": "NULLABLE",
"name": "ssn",
"policyTags": {
"names": [
"projects/{Project ID}/locations/us/taxonomies/1234567890/policyTags/3333333333"
]
},
"type": "STRING"
}
]
Insert文
はじめにInsertを試してみます。
Insertを実行するユーザーはBigQuery データ編集者の権限ありますがすべてのポリシータグでアクセス権がない状態です。
また列レベルのアクセス制御で保護されているテーブルではレガシーSQLクエリはすべて拒否されるためGoogleSQLを使用してクエリを実行する必要があります。
$ bq query --use_legacy_sql=false "INSERT INTO access_control.customers VALUES('alice', 85, '123-456-7890');"
Waiting on bqjob_r73c7a79c30e9d6d5_00000191be55b760_1 ... (0s) Current status: DONE
Number of affected rows: 1
列レベルのアクセス制御がなされていても問題なくInsertが実行できました。
Update文
次にUpdateを試してみます。
$ bq query --use_legacy_sql=false "UPDATE access_control.customers SET credit_score = 0 WHERE user_id LIKE 'alice%' AND credit_score < 30"
Waiting on bqjob_r633ecf89465b0392_00000191be59ebab_1 ... (0s) Current status: DONE
BigQuery error in query operation: Error processing job '{Project ID}:bqjob_r633ecf89465b0392_00000191be59ebab_1': Access Denied: BigQuery BigQuery: User has neither fine-grained reader nor masked get permission to get data protected by policy tag
"data_masking_test : policy-tag-2" on column access_control.customers.credit_score.
想定通り列レベルの制御がなされてUpdateが失敗します。
ここでuser_idの列のみにアクセス権のあるユーザーでWhere句にuser_idだけを指定してUpdate文を実行してみます。
$ bq query --use_legacy_sql=false "UPDATE access_control.customers SET credit_score = 0 WHERE user_id LIKE 'alice%'"
Waiting on bqjob_r28a01a4693626502_00000191be5ef368_1 ... (0s) Current status: DONE
Number of affected rows: 4
Where句で指定されている列にアクセス権のある場合はUpdateも成功します。
Delete文
Deleteを試してみます。
$ bq query --use_legacy_sql=false "DELETE access_control.customers WHERE credit_score = 0"
Waiting on bqjob_r47b82b6ec7af339e_00000191be63a119_1 ... (0s) Current status: DONE
BigQuery error in query operation: Error processing job '{Project ID}:bqjob_r47b82b6ec7af339e_00000191be63a119_1': Access Denied: BigQuery BigQuery: User has neither fine-grained reader nor masked get permission to get data protected by policy tag
"data_masking_test : policy-tag-2" on column access_control.customers.credit_score.
Where句でアクセス制限がなされている列を参照しているため失敗します。
こちらもuser_idの列のみにアクセス権のあるユーザーでWhere句にuser_idだけを指定してDelete文を実行してみます。
$ bq query --use_legacy_sql=false "DELETE access_control.customers WHERE user_id like 'alice%'"
Waiting on bqjob_r49c745ff802880ba_00000191be620bf7_1 ... (0s) Current status: DONE
Number of affected rows: 4
この場合はDeleteの実行が成功します。
bq load
次にloadを試してみます。
読み込むのは以下のファイルです。
taro,750,123-45-6789
hanako,680,987-65-4321
[
{
"name": "user_id",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "credit_score",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "ssn",
"type": "STRING",
"mode": "NULLABLE"
}
]
bq loadでローカルからloadを実行します。
$ bq load --source_format=CSV access_control.customers ./customers_data.csv ./customers_schema.json
Upload complete.
Waiting on bqjob_r1be13a6d9a602d97_00000191be6b5173_1 ... (0s) Current status: DONE
列レベル制御に関係なくloadを行えました。Insertが問題なかったのでこちらも問題ないと予想はできていました。
bq copy
テーブルのコピーを試してみます。
$ bq cp -a access_control.customers access_control.customers_dest
BigQuery error in cp operation: Error processing job '{Project ID}:bqjob_r1f8b3564d5ef149a_00000191be714cf1_1': Access Denied: Policy tag projects/{Project ID}/locations/us/taxonomies/1234567890/policyTags/2222222222: User does not have
permission to access data protected by policy tag "data_masking_test : policy-tag-2" attached to table(s) {Project ID}.access_control.customers
こちらは列レベルの制御が効いて失敗します。
別のユーザーでcustomers_destテーブルを作成したうえでdestテーブルへのクエリ結果の保存を試してみます。
$ bq query --use_legacy_sql=false \
--max_rows=0 \
--destination_table access_control.customers_dest \
--append_table "SELECT * FROM access_control.customers LIMIT 10;"
Waiting on bqjob_r698c85e60160e889_00000191be75d1d1_1 ... (0s) Current status: DONE
BigQuery error in query operation: Error processing job '{Project ID}:bqjob_r698c85e60160e889_00000191be75d1d1_1': Access Denied: BigQuery BigQuery: User has neither fine-grained reader nor masked get permission to get data protected by policy tag
"data_masking_test : policy-tag-3" on column access_control.customers.ssn. User has neither fine-grained reader nor masked get permission to get data protected by policy tag "data_masking_test : policy-tag-2" on column access_control.customers.credit_score.
こちらもSELECT文で列レベル制御をされている列を参照するため失敗します。
Truncate
Truncateを実行してみます。
$ bq query --use_legacy_sql=false "TRUNCATE TABLE access_control.customers"
Waiting on bqjob_rd7ff7268c5f8234_00000191be7925aa_1 ... (0s) Current status: DONE
Number of affected rows: 21
列の参照が無いため成功します。
CREATE VIEW
Viewを作成してみます。
$ bq query --use_legacy_sql=false "CREATE OR REPLACE VIEW access_control.customer_credit_categories AS
SELECT
user_id,
credit_score,
CASE
WHEN credit_score >= 800 THEN 'Excellent'
WHEN credit_score >= 740 THEN 'Very Good'
WHEN credit_score >= 670 THEN 'Good'
WHEN credit_score >= 580 THEN 'Fair'
ELSE 'Poor'
END AS credit_category
FROM
access_control.customers"
Waiting on bqjob_r17ae8d5626d7ce08_00000191be7c3444_1 ... (0s) Current status: DONE
Created {Project ID}.access_control.customer_credit_categories
SELECT中で列レベル制御をされている列を使用していますが直接参照しているわけではないので成功します。
ただしこのように作成したViewであってもSELECTで列レベル制御をされている列を参照しようとすると失敗します。
$ bq query --use_legacy_sql=false 'select * from access_control.customer_credit_categories limit 10;'
BigQuery error in query operation: Error processing job '{Project ID}:bqjob_r5065186b5516ab3b_00000191be7deabd_1': Access Denied: BigQuery BigQuery: User has neither fine-grained reader nor masked get permission to get data protected by policy tag
"data_masking_test : policy-tag-2" on column {Project ID}.access_control.customers.credit_score.
まとめ
BigQueryのアクセス制限において、データのポリシータグを使用した列レベルの制御を実装したテーブルで様々な操作を試験的に実行しました。その結果、SELECTステートメントやWHERE句で列レベル制御が適用されている列を直接参照する場合は、アクセス制御が確実に機能することが確認できました。一方、これらの制御対象列を直接参照しない操作の場合は、制限なく成功することがわかりました。このような列レベル制御を適切に活用することで、きめ細かいアクセス管理が可能となり、よりセキュアにデータを取り扱うことができます。
最後まで読んで頂いてありがとうございました。
